import sqlite3
from config.settings import DEFAULT_DB_PATH
from typing import List, Dict, Any, Optional
from utils.db_pool import db_pool
from utils.logger import logger

class DatabaseManager:
    def __init__(self):
        self._db_path = DEFAULT_DB_PATH
        self._is_connected = False

    @property
    def DB_PATH(self):
        return self._db_path

    @property
    def is_connected(self):
        return self._is_connected and db_pool.is_initialized

    def connect(self) -> bool:
        """连接到数据库，返回是否连接成功"""
        try:
            success = db_pool.initialize(self._db_path)
            self._is_connected = success
            if success:
                logger.info(f"成功连接到数据库: {self._db_path}")
            return success
        except Exception as e:
            logger.error(f"数据库连接错误: {e}")
            self._is_connected = False
            return False

    def close(self):
        """关闭数据库连接"""
        if self.is_connected:
            db_pool.close_all()
            self._is_connected = False
            logger.info("数据库连接已关闭")

    def execute_query(self, query, params=None, max_retries=3, retry_delay=0.5):
        """执行查询并返回结果，支持重试"""
        conn = None
        last_error = None
        
        for attempt in range(max_retries):
            try:
                conn = db_pool.get_connection()
                if not conn:
                    raise ConnectionError("无法获取数据库连接")
                    
                cursor = conn.cursor()
                if params:
                    cursor.execute(query, params)
                else:
                    cursor.execute(query)
                result = cursor.fetchall()
                return result
                
            except sqlite3.OperationalError as e:
                last_error = e
                if "database is locked" in str(e).lower():
                    # 只重试锁定错误
                    logger.warning(f"数据库锁定，尝试重试 ({attempt+1}/{max_retries}): {e}")
                    import time
                    time.sleep(retry_delay * (attempt + 1))  # 指数退避
                    continue
                else:
                    # 其他操作错误直接抛出
                    raise
            finally:
                if conn:
                    db_pool.release_connection(conn)
        
        # 所有重试都失败
        if last_error:
            logger.error(f"查询失败，已重试{max_retries}次: {last_error}")
            raise last_error
        else:
            raise Exception("执行查询失败，原因未知")
    
    def execute_update(self, query, params=None, max_retries=3, retry_delay=0.5):
        """执行更新操作并提交，支持重试"""
        conn = None
        last_error = None
        
        for attempt in range(max_retries):
            try:
                conn = db_pool.get_connection()
                if not conn:
                    raise ConnectionError("无法获取数据库连接")
                    
                cursor = conn.cursor()
                if params:
                    cursor.execute(query, params)
                else:
                    cursor.execute(query)
                conn.commit()
                return cursor.rowcount
                
            except sqlite3.OperationalError as e:
                last_error = e
                if "database is locked" in str(e).lower():
                    # 只重试锁定错误
                    logger.warning(f"数据库锁定，尝试重试 ({attempt+1}/{max_retries}): {e}")
                    import time
                    time.sleep(retry_delay * (attempt + 1))  # 指数退避
                    if conn:
                        try:
                            conn.rollback()  # 回滚任何未完成的事务
                        except:
                            pass
                    continue
                else:
                    # 其他操作错误直接抛出
                    raise
            finally:
                if conn:
                    db_pool.release_connection(conn)
        
        # 所有重试都失败
        if last_error:
            logger.error(f"更新失败，已重试{max_retries}次: {last_error}")
            raise last_error
        else:
            raise Exception("执行更新失败，原因未知")

    def get_tables(self) -> List[str]:
        """获取所有表名"""
        if not self.is_connected:
            return []
        
        try:
            result = self.execute_query("SELECT name FROM sqlite_master WHERE type='table'")
            return [row[0] for row in result]
        except Exception as e:
            logger.error(f"获取表名错误: {e}")
            return []

    def get_table_data(self, table_name: str) -> List[Dict[str, Any]]:
        """获取指定表的所有数据"""
        if not self.is_connected:
            return []
            
        try:
            conn = db_pool.get_connection()
            if not conn:
                return []
                
            try:
                cursor = conn.cursor()
                cursor.execute(f"SELECT * FROM {table_name}")
                columns = [description[0] for description in cursor.description]
                rows = cursor.fetchall()
                return [dict(zip(columns, row)) for row in rows]
            finally:
                db_pool.release_connection(conn)
        except Exception as e:
            logger.error(f"获取表数据错误: {e}")
            return []

    def update_db_path(self, new_path: str):
        """更新数据库路径"""
        if self._db_path != new_path:
            self._db_path = new_path
            self.close()  # 关闭旧连接

    def get_conn(self) -> Optional[sqlite3.Connection]:
        """获取数据库连接（兼容旧代码）"""
        return db_pool.get_connection() if self.is_connected else None

# 创建全局数据库管理器实例
db_manager = DatabaseManager() 